﻿/********************************************************
 * 类名： GoodsDAL.cs
 * 功能： 商品管理数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2013-12-11	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PSS.Entity.SetManage.BasicData;
using PSS.Utility;

namespace PSS.DAL.SetManage.BasicData
{
    public class GoodsDAL : DbHelperBase
    {
        public bool AddGoods(GoodsEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_Goods]([GoodsId],[GoodsCode],[GoodsName],[GCategoryId],[Specific],[StorageId],[UnitId],[PurchasePrice],[SalePrice],[Remark]) ");
            strSql.Append(" VALUES(@GoodsId,@GoodsCode,@GoodsName,@GCategoryId,@Specific,@StorageId,@UnitId,@PurchasePrice,@SalePrice,@Remark) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", model.GoodsId);
                    Db.AddInParameter(cmd, "@GoodsCode", model.GoodsCode);
                    Db.AddInParameter(cmd, "@GoodsName", model.GoodsName);
                    Db.AddInParameter(cmd, "@GCategoryId", model.GCategoryId);
                    Db.AddInParameter(cmd, "@Specific", model.Specific);
                    Db.AddInParameter(cmd, "@StorageId", model.StorageId);
                    Db.AddInParameter(cmd, "@UnitId", model.UnitId);
                    Db.AddInParameter(cmd, "@PurchasePrice", model.PurchasePrice);
                    Db.AddInParameter(cmd, "@SalePrice", model.SalePrice);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public void AddGoodsDetail(List<GoodsDetailEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapDetailId =
                    new SqlBulkCopyColumnMapping("DetailId", "DetailId");
                bcp.ColumnMappings.Add(mapDetailId);

                var mapGoodsId =
                    new SqlBulkCopyColumnMapping("GoodsId", "GoodsId");
                bcp.ColumnMappings.Add(mapGoodsId);

                var mapStorageId =
                    new SqlBulkCopyColumnMapping("StorageId", "StorageId");
                bcp.ColumnMappings.Add(mapStorageId);

                var mapInitialNumber =
                    new SqlBulkCopyColumnMapping("InitialNumber", "InitialNumber");
                bcp.ColumnMappings.Add(mapInitialNumber);

                var mapUnitCost =
                   new SqlBulkCopyColumnMapping("UnitCost", "UnitCost");
                bcp.ColumnMappings.Add(mapUnitCost);

                var mapInitialPrice =
                   new SqlBulkCopyColumnMapping("InitialPrice", "InitialPrice");
                bcp.ColumnMappings.Add(mapInitialPrice);

                bcp.DestinationTableName = "PSS_GoodsDetail";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public int GetTotal(GoodsQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(*) AS [Total] FROM PSS_Goods AS A  ");
            strSql.Append(" INNER JOIN PSS_GoodsCategory AS B ON A.GCategoryId=B.GCategoryId ");
            strSql.Append(" INNER JOIN PSS_Storage AS C ON A.StorageId=C.StorageId ");
            strSql.Append(" INNER JOIN PSS_Unit AS D ON A.UnitId=D.UnitId ");
            strSql.Append(" LEFT JOIN (SELECT GoodsId,SUM(UnitCost) AS TotalUnitCost,SUM(InitialPrice) AS TotalInitialPrice  FROM PSS_GoodsDetail GROUP BY GoodsId) AS X ON X.GoodsId=A.GoodsId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.GCategoryId))
            {
                strSql.Append(" AND A.GCategoryId=@GCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.GoodsCode))
            {
                strSql.Append(" AND A.GoodsCode LIKE '%" + qcmodel.GoodsCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.GoodsName))
            {
                strSql.Append(" AND A.GoodsName LIKE '%" + qcmodel.GoodsName + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Specific))
            {
                strSql.Append(" AND A.Specific LIKE '%" + qcmodel.Specific + "%' ");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.GCategoryId))
                    {
                        Db.AddInParameter(cmd, "@GCategoryId", qcmodel.GCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<GoodsEntity> GetGoodsList(GoodsQcEntity qcmodel)
        {
            var list = new List<GoodsEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.CreateTime) AS idx,A.GoodsId,B.GCategoryName,A.GoodsCode,A.GoodsName,A.Specific,D.UnitName,A.StorageId,C.StorageName,X.TotalInitialNumber,X.TotalUnitCost,X.TotalInitialPrice,A.PurchasePrice,A.SalePrice FROM PSS_Goods AS A  ");
            strSql.Append(" INNER JOIN PSS_GoodsCategory AS B ON A.GCategoryId=B.GCategoryId ");
            strSql.Append(" INNER JOIN PSS_Storage AS C ON A.StorageId=C.StorageId ");
            strSql.Append(" INNER JOIN PSS_Unit AS D ON A.UnitId=D.UnitId ");
            strSql.Append(" LEFT JOIN (SELECT GoodsId,SUM(InitialNumber) AS TotalInitialNumber,SUM(UnitCost) AS TotalUnitCost,SUM(InitialPrice) AS TotalInitialPrice  FROM PSS_GoodsDetail GROUP BY GoodsId) AS X ON X.GoodsId=A.GoodsId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.GCategoryId))
            {
                strSql.Append(" AND A.GCategoryId=@GCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.GoodsCode))
            {
                strSql.Append(" AND A.GoodsCode LIKE '%" + qcmodel.GoodsCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.GoodsName))
            {
                strSql.Append(" AND A.GoodsName LIKE '%" + qcmodel.GoodsName + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Specific))
            {
                strSql.Append(" AND A.Specific LIKE '%" + qcmodel.Specific + "%' ");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.GCategoryId))
                    {
                        Db.AddInParameter(cmd, "@GCategoryId", qcmodel.GCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new GoodsEntity();
                            if (reader["GoodsId"] != DBNull.Value)
                                model.GoodsId = new Guid(reader["GoodsId"].ToString());
                            model.GCategoryName = reader["GCategoryName"] == obj ? null : Convert.ToString(reader["GCategoryName"]);
                            model.GoodsCode = reader["GoodsCode"] == obj ? null : Convert.ToString(reader["GoodsCode"]);
                            model.GoodsName = reader["GoodsName"] == obj ? null : Convert.ToString(reader["GoodsName"]);
                            model.Specific = reader["Specific"] == obj ? null : Convert.ToString(reader["Specific"]);
                            model.UnitName = reader["UnitName"] == obj ? null : Convert.ToString(reader["UnitName"]);
                            if (reader["StorageId"] != DBNull.Value)
                                model.StorageId = new Guid(reader["StorageId"].ToString());
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (reader["TotalInitialNumber"] != DBNull.Value)
                                model.InitialNumber = Convert.ToDecimal(reader["TotalInitialNumber"].ToString());
                            if (reader["TotalUnitCost"] != DBNull.Value)
                                model.UnitCost = Convert.ToDecimal(reader["TotalUnitCost"].ToString());
                            if (reader["TotalInitialPrice"] != DBNull.Value)
                                model.InitialPrice = Convert.ToDecimal(reader["TotalInitialPrice"].ToString());
                            if (reader["PurchasePrice"] != DBNull.Value)
                                model.PurchasePrice = Convert.ToDecimal(reader["PurchasePrice"].ToString());
                            if (reader["SalePrice"] != DBNull.Value)
                                model.SalePrice = Convert.ToDecimal(reader["SalePrice"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public GoodsEntity GetGoodsInfo(string id)
        {
            var model = new GoodsEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [GoodsId],[GoodsCode],[GoodsName],[GCategoryId],[Specific],[StorageId],[UnitId],[PurchasePrice],[SalePrice],[Remark] FROM [PSS_Goods] where GoodsId=@GoodsId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["GoodsId"] != DBNull.Value)
                                model.GoodsId = new Guid(reader["GoodsId"].ToString());
                            model.GoodsCode = reader["GoodsCode"] == obj ? null : Convert.ToString(reader["GoodsCode"]);
                            model.GoodsName = reader["GoodsName"] == obj ? null : Convert.ToString(reader["GoodsName"]);
                            if (reader["GCategoryId"] != DBNull.Value)
                                model.GCategoryId = new Guid(reader["GCategoryId"].ToString());
                            if (reader["StorageId"] != DBNull.Value)
                                model.StorageId = new Guid(reader["StorageId"].ToString());
                            if (reader["UnitId"] != DBNull.Value)
                                model.UnitId = new Guid(reader["UnitId"].ToString());
                            if (reader["PurchasePrice"] != DBNull.Value)
                                model.PurchasePrice = Convert.ToDecimal(reader["PurchasePrice"].ToString());
                            if (reader["SalePrice"] != DBNull.Value)
                                model.SalePrice = Convert.ToDecimal(reader["SalePrice"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public IList<GoodsDetailEntity> GetGoodsDetailList(GoodsDetailQcEntity qcmodel)
        {
            var list = new List<GoodsDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT A.[DetailId],A.[StorageId],B.StorageName,A.[InitialNumber],A.[UnitCost],A.[InitialPrice] FROM PSS_GoodsDetail AS A  ");
            strSql.Append(" INNER JOIN PSS_Storage AS B ON A.StorageId=B.StorageId ");
            strSql.Append(" WHERE GoodsId=@GoodsId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", qcmodel.GoodsId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new GoodsDetailEntity();
                            if (reader["DetailId"] != DBNull.Value)
                                model.DetailId = new Guid(reader["DetailId"].ToString());
                            if (reader["StorageId"] != DBNull.Value)
                                model.StorageId = new Guid(reader["StorageId"].ToString());
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (reader["InitialNumber"] != DBNull.Value)
                                model.InitialNumber = Convert.ToDecimal(reader["InitialNumber"].ToString());
                            if (reader["UnitCost"] != DBNull.Value)
                                model.UnitCost = Convert.ToDecimal(reader["UnitCost"].ToString());
                            if (reader["InitialPrice"] != DBNull.Value)
                                model.InitialPrice = Convert.ToDecimal(reader["InitialPrice"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyGoods(GoodsEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Goods] SET [GoodsCode] = @GoodsCode,[GoodsName] = @GoodsName,[GCategoryId] = @GCategoryId,[Specific] = @Specific,[StorageId] = @StorageId,[UnitId] = @UnitId,[PurchasePrice] = @PurchasePrice,[SalePrice] = @SalePrice,[Remark] = @Remark WHERE [GoodsId] = @GoodsId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsCode", model.GoodsCode);
                    Db.AddInParameter(cmd, "@GoodsName", model.GoodsName);
                    Db.AddInParameter(cmd, "@GCategoryId", model.GCategoryId);
                    Db.AddInParameter(cmd, "@Specific", model.Specific);
                    Db.AddInParameter(cmd, "@StorageId", model.StorageId);
                    Db.AddInParameter(cmd, "@UnitId", model.UnitId);
                    Db.AddInParameter(cmd, "@PurchasePrice", model.PurchasePrice);
                    Db.AddInParameter(cmd, "@SalePrice", model.SalePrice);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@GoodsId", model.GoodsId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool DeleteGoodsDetail(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_GoodsDetail] WHERE GoodsId=@GoodsId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveGoods(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_Goods] WHERE GoodsId=@GoodsId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
